from PySide6.QtWidgets import QWidget,QMessageBox,QTableWidgetItem,QMenu,QAbstractItemView,QHeaderView,QFileDialog
from PySide6.QtCore import Qt
from sqlalchemy.orm import Session
import pandas as pd

from settings.initsetting.mode.initsetting import InitSetting
from settings.publicvar import engine
from settings.publicFuncation.exceldownloadimport import OperationModel

from views.book.books.books import Ui_books
from views.book.mode.book import Books, Author, Publisher,Category
from views.book.books.operationbookView import OperationBookView
from views.book.download.bookdownloaddataView import BookDownloadDataView

class BooksView(QWidget,Ui_books):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.bookstable.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.bookstable.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.bookstable.horizontalHeader().setSectionResizeMode(QHeaderView.Interactive)
        self.bookstable.setContextMenuPolicy(Qt.CustomContextMenu)
        self.bookstable.customContextMenuRequested.connect(self.generate_menu)

        self.addbtn.clicked.connect(self.addFun)
        self.downloadmodle.clicked.connect(self.downloadmodleFun)
        self.importbtn.clicked.connect(self.importdataFun)
        self.serearchbtn.clicked.connect(self.serearchFun)
        self.sereachedit.editingFinished.connect(self.serearchFun)
        self.downloaddata.clicked.connect(self.downloaddataFun)
        self.loadTableData()

        # 表格结构
    def SetTableStructure(self, data):
        self.bookstable.setRowCount(len(data))
        for i, item in enumerate(data):
            idx = QTableWidgetItem(str(item.id))
            self.bookstable.setItem(i, 0, idx)
            isbn = QTableWidgetItem(item.isbn)
            self.bookstable.setItem(i, 1, isbn)
            title = QTableWidgetItem(item.title)
            self.bookstable.setItem(i, 2, title)
            if item.author:
                author = QTableWidgetItem(item.author.name)
                self.bookstable.setItem(i, 3, author)
            if item.publisher:
                publisher = QTableWidgetItem(item.publisher.name)
                self.bookstable.setItem(i, 4, publisher)
            publish_date = QTableWidgetItem(str(item.publish_date))
            self.bookstable.setItem(i, 5, publish_date)
            summary = QTableWidgetItem(item.summary)
            self.bookstable.setItem(i, 6, summary)
            price = QTableWidgetItem(str(item.price))
            self.bookstable.setItem(i, 7, price)
            amount = QTableWidgetItem(str(item.amount))
            self.bookstable.setItem(i,8, amount)
            if item.category:
                category= QTableWidgetItem(item.category.name)
                self.bookstable.setItem(i,9 , category)
            create_time= QTableWidgetItem(str(item.create_date))
            self.bookstable.setItem(i,10 , create_time)
            update_time= QTableWidgetItem(str(item.update_date))
            self.bookstable.setItem(i,11 , update_time)
            mark= QTableWidgetItem(item.mark)
            self.bookstable.setItem(i,12 , mark)

    # 加载表格数据
    def loadTableData(self):
        with Session(engine) as session, session.begin():
            books = session.query(Books).all()
            self.SetTableStructure(books)

    # 模板下载
    def downloadmodleFun(self):
        OperationModel().downloadmodleFun('图书模板.xlsx')

    # 导入数据
    def importdataFun(self):
        names = ['idx','isbn','title','author','publisher','publish_date','summary','price','amount','category','create_date','update_date','mark']
        dtypes = {'isbn':str,'price': float, 'amount': int}
        parse_dates=['publish_date','create_date','update_date']
        data = OperationModel().importdataFun(names, dtypes,parse_dates=parse_dates)
        with Session(engine) as session,session.begin():
            # 作者
            author_all = session.query(Author).all()
            author_name = pd.Series([item.name for item in author_all])
            author_name = pd.Series([item.name for item in author_all])
            data_author = data.author
            authors = set(data_author)^set(author_name)
            authors.discard('')
            authors = [Author(name=item) for item in authors]
            session.add_all(authors)

            # 出版社
            pulisher_all = session.query(Publisher).all()
            publisher_name = pd.Series([item.name for item in pulisher_all])
            data_publisher = data.publisher
            publishers = set(publisher_name)^set(data_publisher)
            publishers.discard('')
            publishers = [Publisher(name=item) for item in publishers]
            session.add_all(publishers)
            #

            # 分类
            category_all = session.query(Category).all()
            category_name = pd.Series([item.name for item in category_all])
            data_category = data.category
            categorys = set(category_name)^set(data_category)
            categorys.discard('')
            categorys = [Category(name=item) for item in categorys]
            session.add_all(categorys)

        with Session(engine) as session,session.begin():
            # 存储数据处理
            save_books = data
            author_all = session.query(Author).all()
            authors = pd.DataFrame([(item.id, item.name) for item in author_all]).rename(
                columns={0: 'author_id', 1: 'author'})
            if not authors.empty:
                save_books = pd.merge(save_books, authors, on='author', how='left')
            pulisher_all = session.query(Publisher).all()
            publishers = pd.DataFrame([(item.id, item.name) for item in pulisher_all]).rename(
                columns={0: 'publisher_id', 1: 'publisher'})
            if not publishers.empty:
                save_books = pd.merge(save_books, publishers, on='publisher', how='left')
            category_all = session.query(Category).all()
            categorys = pd.DataFrame([(item.id, item.name) for item in category_all]).rename(
                columns={0: 'category_id', 1: 'category'})
            if not categorys.empty:
                save_books = pd.merge(save_books, categorys, on='category', how='left')
            save_books['publish_date'] = save_books['publish_date'].apply(self.date_dell)
            save_books['create_date'] = save_books['create_date'].apply(self.date_dell)
            save_books['update_date'] = save_books['update_date'].apply(self.date_dell)
            save_books['author_id'] = save_books['author_id'].apply(self.int_dell)
            save_books['publisher_id'] = save_books['publisher_id'].apply(self.int_dell)
            init_school_id = session.query(InitSetting).one()
            save_books['school_id'] = init_school_id.school_id

            books = session.query(Books.isbn).filter(Books.isbn.in_(save_books.isbn.to_list()))
            book_tem = list(set([tem[0] for tem in books]))
            save_books = save_books[~save_books['isbn'].isin(book_tem)]

            save_books = save_books.drop(columns=['author', 'publisher', 'category']).to_dict('records')
            save_books = [Books(**item) for item in save_books]
            session.add_all(save_books)
            QMessageBox.information(None,'成功','成功%s条，失败%s条'%(len(save_books),len(data)-len(save_books)))
        self.loadTableData()

    def date_dell(self,data):
        tem = None
        if len(data) >= 19:
            tem = pd.to_datetime(data).date()
        return tem

    def int_dell(self,data):
        tem = -1
        if not pd.isna(data):
            tem = int(data)
        return tem

    def addFun(self):
        save = OperationBookView('add',False)
        save.exec()

    # 表格菜单
    def generate_menu(self,pos):
        menu = QMenu()
        viewitem = menu.addAction('查看')
        changeitem = menu.addAction('修改')
        deletitem = menu.addAction('删除')
        action = menu.exec(self.bookstable.mapToGlobal(pos))
        posrow = self.bookstable.currentRow()
        columns = self.bookstable.columnCount()
        data = []
        for i in range(columns):
            item = self.bookstable.item(posrow,i)
            if item is not None:
                data.append(item.text())
            else:
                data.append('')
        if action == changeitem:
            change = OperationBookView('change',data)
            change.exec()
        elif action == deletitem:
            delete =  OperationBookView('delete',data)
            delete.exec()
        elif action == viewitem:
            view = OperationBookView('view',data)
            view.exec()
        self.loadTableData()

    # 搜索框数据
    def serearchFun(self):
        sereachdata = self.sereachedit.text().strip()
        if sereachdata == '':
            QMessageBox.critical(None, '错误', '你没有搜入数据！')
            self.loadTableData()
        else:
            with Session(engine) as session, session.begin():
                sereachTitle = session.query(Books).filter(
                    Books.title.like('%' + sereachdata + '%')).all()
                sereachIsbn = session.query(Books).filter(
                    Books.isbn.like('%' + sereachdata + '%')).all()
                sereachData = sereachTitle + sereachIsbn
                if sereachData:
                    self.SetTableStructure(sereachData)
                else:
                    QMessageBox.critical(None,'没有数据','你搜索数据不存在！')

    # 数据导出
    def downloaddataFun(self):
        BookDownloadDataView().exec()